from django.contrib.auth.models import User
from django.template.loader import get_template
from config.conf import *

# Create your models here.

def getAllCategory():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select cid, name from category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
def getAllBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getBookFromName(bName):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and e.title='%s'" %(bName)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getBookFromCategory(cName):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and c.name='%s'" %(cName)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getBookOfCategory(cid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and c.cid = %s" % (cid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getCategoryID():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT * FROM category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]


def addNewCategory(name, weight, status):
    db = MySQLdb.connect('localhost', 'root', 'root', 'bookstore', charset="utf8", use_unicode=True)
    cursor = db.cursor()
    sql = """INSERT INTO category( name, weight, status)
        VALUES ('%s', '%s', '%s')""" % (name, weight, status)
    cursor.execute(sql)
    db.commit()
    db.close()
    return

def getAllUser():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from auth_user"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getAllStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title,e.created from entity as e, static as s WHERE e.nid = s.nid"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows	

def getAllSetting():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select configuration.id,configuration.key,configuration.value from configuration"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows		

def getDetailStatic(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select detail,value from static WHERE nid = %s" %(id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]

def getDetailSetting(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select c.key,c.value from configuration as c WHERE id = %s" %(id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]	

def submitEditStatic(id,detail):
    db = initDataBase()
    cursor = db.cursor()
    sql = "UPDATE static SET detail = '%s' WHERE nid = %s" %(detail,id)
    cursor.execute(sql)
    db.commit()
    db.close()
    return

def submitEditSetting(id,detail):
    db = initDataBase()
    cursor = db.cursor()
    sql = "UPDATE configuration as c SET c.value = '%s' WHERE id = %s" %(detail,id)
    cursor.execute(sql)
    db.commit()
    db.close()
    return	

def getBookName():
    listName =''
    db = initDataBase()
    cursor = db.cursor()
    sql = "select title from entity Where type ='book'"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    for row in rows:
        listName += '"%s",' %(row[0])
    return listName

def getCatName():
    listName =''
    db = initDataBase()
    cursor = db.cursor()
    sql = "select name from category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    for row in rows:
        listName += '"%s",' %(row[0])
    return listName

